package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.dao.ColumnDao;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.model.Column;
import com.powerunion.datacollection.model.SubTblColumn;
import com.powerunion.datacollection.model.jsonObject.SubColumnTreeNode;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;
import com.powerunion.util.query.jdbc.QueryParameters;

public class ColumnDaoImpl extends JdbcBase implements ColumnDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			ColumnDaoImpl.class);

	public boolean delete(String code) throws DaoException {
		String sql = "delete from dc_template_column where form_id = ? ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);

		update.addParameter(new SqlParameter(SqlType.VARCHAR, code));
		try {
			update.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("delete throw error!");
		}

		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "列信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "列信息"
						+ Constants.FAIL);
			}
			return false;
		}
	}

	public Column getBykey(String id) throws DaoException {
		Column column = new Column();
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select id,column_name,field_name,column_width,i_hidden,ordr_no,form_id,align ")
				.append(" from dc_template_column where id = ? ");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, id));
		try {
			query.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getBykey throw error!");
		}

		if (query.resultSetAvailable()) {// 检查结果集是否可用
			// 获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				// column.setId(rs.getFieldValueAsString("id"));
				column.setHeader(rs.getFieldValueAsString("column_name"));
				column.setWidth(rs.getFieldValueAsInteger("column_width")
						.intValue());
				column.setDisplayOrder(rs.getFieldValueAsInteger("ordr_no")
						.intValue());
				column.setDataIndex(rs.getFieldValueAsString("field_name"));
				column.setHidden(rs.getFieldValueAsInteger("i_hidden")
						.intValue());
				column.setFormId(rs.getFieldValueAsString("form_id"));
				column.setAlign(rs.getFieldValueAsString("align"));
			}
			rs.clearAll();
		}
		return column;
	}

	public List getColumns(String formId) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "getColumns");
		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select column_name,field_name,column_width,i_hidden,ordr_no,form_id,align,field_type ")
				.append(
						" from dc_template_column where form_id = ? order by ordr_no");
		QueryParameters params = new QueryParameters();
		params.addParameter(formId, Types.VARCHAR);

		String sql = sqlStr.toString();
		List list = null;
		try {
			list = this.list(sql, params, "getColumnInfo");
		} catch (Exception e) {
			logger.error("getColumns:" + Constants.SEARCH + "Columns"
					+ Constants.FAIL, e);
			throw new DaoException("search db record error!");
		}
		logger.debug("getColumns" + Constants.SEARCH + "Columns"
				+ Constants.SUCCESS);
		return list;
	}

	public final Column getColumnInfo(ResultSet rs) throws SQLException {
		Column column = new Column();
		column.setHeader(rs.getString("column_name"));
		column.setWidth(rs.getInt("column_width"));
		column.setDisplayOrder(rs.getInt("ordr_no"));
		column.setDataIndex(rs.getString("field_name"));
		column.setHidden(rs.getInt("i_hidden"));
		column.setFormId(rs.getString("form_id"));
		column.setAlign(rs.getString("align"));
		column.setFieldType(rs.getString("field_type"));

		return column;
	}

	public boolean save(List Columns, String formId) throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql.append(
						"insert into dc_template_column(id,column_name,field_name,column_width,i_hidden,ordr_no,form_id,align,field_type)")
				.append(" values(?,?,?,?,?,?,?,?,?)");

		UpdateOperator update = new UpdateOperator();
		update.setSql(sql.toString());

		for (Iterator iter = Columns.iterator(); iter.hasNext();) {
			Column column = (Column) iter.next();
			SqlParameterSet spSet = new SqlParameterSet();
			String id = String.valueOf(this.getSeq("dc_template_column"));
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, id));
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, column
					.getHeader()));
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, column.getDataIndex()));
			spSet.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					column.getWidth())));
			spSet.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					column.getHidden())));
			spSet.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					column.getDisplayOrder())));
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, column
					.getAlign()));
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, column.getFieldType()));

			update.addBatchParameter(spSet);
		}
		try {
			update.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("save throw error!");
		}

		int[] effectCount = update.getBatchEffectCounts();
		if (effectCount != null && effectCount.length > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "列信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "列信息" + Constants.FAIL);
			}
			return false;
		}

	}

	public int getColumnNumById(String formId) throws DaoException {
		String sql = "select count(*) from dc_template_column where form_id=?";
		QueryOperator query = new QueryOperator();
		query.setSql(sql);
		query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		try {
			query.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getColumnNumById throw error!");
		}

		int columnNum = 0;
		if (query.resultSetAvailable()) {// 检查结果集是否可用
			// 获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				columnNum = rs.getFieldValueAsInteger(1).intValue();
			}
			rs.clearAll();
		}
		return columnNum;
	}

	private boolean delete4header(String code) throws DaoException {
		String sql = "delete from dc_grouheader_column where form_id = ? ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);

		update.addParameter(new SqlParameter(SqlType.VARCHAR, code));
		try {
			update.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("delete throw error!");
		}

		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {
			return true;
		} else {
			return false;
		}
	}
	public boolean save(SubTblColumn stc, String formId) throws DaoException {
		this.delete4header(formId);
		Connection conn = null;
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"insert into dc_grouheader_column(id,column_name,field_name,column_width,i_hidden,ordr_no,form_id,align,paraid)")
				.append(" values(?,?,?,?,?,?,?,?,?)");

		String activityId = "";
		try {
			conn = this.getConnection();
			activityId = this.saveSubColumnInfo(stc, sql.toString(), conn,
					"null", formId);
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存从表列头信息出错！");
		} finally {
			DBUtils.close(conn);
		}

		if (!StringUtils.isEmpty(activityId)) {

			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "从表列头信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "从表列头信息"
						+ Constants.FAIL);
			}
			return false;
		}
	}

	private String saveSubColumnInfo(SubTblColumn stc, String sql,
			Connection conn, String paraId, String formId) throws DaoException {

		String resultId = "";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			int i = 1;
			resultId = String.valueOf(this.getSeq("dc_grouheader_column"));
			ps.setString(i++, resultId);
			ps.setString(i++, stc.getHeader());
			if (StringUtils.isEmpty(stc.getTableField())) {
				ps.setString(i++, "none");
			} else {
				ps.setString(i++, stc.getTableField());
			}
			ps.setInt(i++, stc.getColumnWidth());
			ps.setInt(i++, stc.getIhidden());
			ps.setInt(i++, stc.getDisplayOrder());
			ps.setString(i++, formId);
			ps.setString(i++, stc.getAlign());
			ps.setString(i++, paraId);

			ps.executeUpdate();
			ps.close();
			if (stc.getData() != null && stc.getData().size() > 0) {
				for (Iterator iter2 = stc.getData().iterator(); iter2.hasNext();) {
					SubTblColumn ai = (SubTblColumn) iter2.next();
					String aiId = "";
					// 递归保存
					aiId = this.saveSubColumnInfo(ai, sql, conn, resultId,
							formId);
					if (StringUtils.isEmpty(aiId))
						break;
				}
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存从表列头信息出错！");
		} finally {
			DBUtils.close(ps);
		}
		return resultId;
	}

	public List getSubFormColumns(String formId) throws Exception {
		Connection conn = null;
		StringBuffer sqlString = new StringBuffer();

		sqlString
				.append(
						"select id,column_name,field_name,column_width,i_hidden,ordr_no,align,paraid ")
				.append(
						" from dc_grouheader_column where form_id=? and paraid=? ");

		List treeNodes = null;
		try {
			conn = this.getConnection();
			treeNodes = new ArrayList();
			/**
			 * 遍历的时候根节点不取出来，且根节点下的子节点的paraId属性都设置成“root”，这样做的目的是为了展现的时候不出现多个"根节点"，
			 * 把数据库里的节点都统一放在前台默认的根节点下
			 */
			treeNodes = this.getTreeNodes(conn, formId, "null", sqlString
					.toString(), treeNodes);
		} catch (Exception e) {
			logger.error("", e);
			throw new Exception(Constants.SEARCH + "getSubFormColumns tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(conn);
		}
		return treeNodes;
	}

	private List getTreeNodes(Connection conn, String formId, String paraId,
			String sql, List trees) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, formId);
			ps.setString(2, paraId);
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {
				String id = rs.getString("id");
				String header = rs.getString("column_name");
				String fieldName = rs.getString("field_name");
				int width = rs.getInt("column_width");
				int hidden = rs.getInt("i_hidden");
				String align = rs.getString("align");
				String paraid = rs.getString("paraid");

				SubColumnTreeNode stc = new SubColumnTreeNode();
				stc.setId(id);
				stc.setText(header);
				stc.setTableField(fieldName);
				stc.setColumnWidth(width);
				stc.setIhidden(hidden);
				stc.setAlign(align);
				if(trees != null){//二级节点
					stc.setParaId("root");
				}else{
					stc.setParaId(paraid);
				}
			
				List atChildren = new ArrayList();
				// 递归调用
				if (paraId == "null") {
					trees = this.getTreeNodes(conn, formId, id, sql, trees);
				} else {
					atChildren = this.getTreeNodes(conn, formId, id, sql, null);
				}

				if (!atChildren.isEmpty()) {
					stc.setChildren(atChildren);
					stc.setLeaf(false);
				} else {
					stc.setLeaf(true);
				}
				if (paraId != "null") {
					if (trees == null) {
						treeNodeArray.add(stc);
					} else {
						trees.add(stc);
					}
				}
			}

			logger.debug(Constants.SEARCH + "subColumn tree"
					+ Constants.SUCCESS);

		} catch (Exception e) {
			logger.error("getTreeNodes() of throws : ", e);
			throw new Exception(Constants.SEARCH + "subColumn tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps);
		}
		return trees == null ? treeNodeArray : trees;
	}
	
}
